﻿/*
 * This software is the confidential and proprietary
 * information of NetBay
 * Such confidential information should not be disclosed
 * NetBay copyright(c) 2010
 */

#region Using
using System;
using NetBay.Core.BusinessEntities;
using Rio.Framework.RioManager;
using Rio.Framework.Settings;
using System.Collections.Generic;
using NetBay.Core.BusinessEntities.AdvancedSearchCriteria;
using System.ComponentModel;
using NetBay.Core.Extensions;
using System.Text;

#endregion

namespace Rio.Framework.Extensions
{

    #region Extensions
    public static class IndexEx
    {

        #region enums

        /// <summary>
        /// 
        /// </summary>
        public enum SqlLogicalOperator
        {
            [DescriptionAttribute("")]
            None,
            [DescriptionAttribute("AND")]
            AND,
            [DescriptionAttribute("OR")]
            OR,
        }

        #endregion

        #region Fields
        private const string NullString = "NULL";
        private const string NullIntegerString = "0";
        #endregion

        #region Functions

        public static bool IsIndexTypeDate(this Index p_oSource)
        {
            if (p_oSource == null)
                throw new ArgumentException("Parameter source can not be null.");

            return p_oSource.Type == 4; //IndexDomainServiceEnums.IndexType.Date:
        }

        /// <summary>
        /// Return an operator to test equality of a value 
        /// </summary>
        /// <param name="p_oSource"></param>
        /// <param name="p_szSource"></param>
        /// <returns></returns>
        public static string EqualityOperatorByValue(this Index p_oSource, string p_szSource)
        {
            if (p_szSource.Equals(NullString))
                return " IS ";
            else
                return " = ";
        }

        /// <summary>
        /// Convert a field index value to SQL search syntax
        /// </summary>
        /// <param name="p_oSource">Index extention</param>
        /// <param name="p_oValue">Value of the index</param>
        /// <param name="p_sTableName">Table name in database</param>
        /// <param name="equalityOperator">Equality operator to use between values</param>
        /// <param name="logicalOperator">Logical operator to use between index</param>
        /// <returns>Exemple: AND TD001_CH001='test'</returns>
        public static string ValueForSQLSearch(this Index p_oSource, string p_oValue, string p_sTableName, SqlLogicalOperator logicalOperator = SqlLogicalOperator.AND, SqlEqualityOperator equalityOperator = SqlEqualityOperator.Equal)
        {
            if (p_oSource == null)
                throw new ArgumentException("Parameter source can not be null.");

            string l_oRet = string.Empty;
            string l_sEqualityOperator = string.Format(" {0} ", AdvancedSearchCriteriaItem.ParseNameOperator(equalityOperator));
            string l_sFullIndexName = p_sTableName + "_CH" + p_oSource.Name; // TD001_CH001

            // Preformatted orperator: " AND " - " OR "
            string l_sLogicalOperator = string.Empty;
            if (!string.IsNullOrEmpty(logicalOperator.szGetDescription()))
            {
                l_sLogicalOperator = string.Format(" {0} ", logicalOperator.szGetDescription()); 
            }

            //it is useless to add a where clause : [FIELD] like '%'    
            if (!p_oValue.Equals("%"))
            {
                switch (p_oSource.Type)
                {
                    case 1:     //IndexDomainServiceEnums.IndexType.String:
                    case 7:     //IndexDomainServiceEnums.IndexType.IntegerString:
                        if (p_oValue.Contains("%"))
                        {
                            if (equalityOperator == SqlEqualityOperator.Equal)
                                l_sEqualityOperator = " like ";
                            else
                                l_sEqualityOperator = " not like ";
                        }

                        l_oRet = Epure(p_oValue);
                        l_oRet = DoubleQuote(l_oRet);
                        l_oRet = EncapsulateQuote(l_oRet);

                        l_oRet = l_sLogicalOperator + l_sFullIndexName + l_sEqualityOperator + l_oRet;
                        break;

                    case 10:    //IndexDomainServiceEnums.IndexType.Groups:
                    case 11:    //IndexDomainServiceEnums.IndexType.ListeOfRecipient:
                    case 12:    //IndexDomainServiceEnums.IndexType.Object: 
                    case 15:    //IndexDomainServiceEnums.IndexType.ListOfSenders: 
                        switch (SettingsManager.cStoreSettings.Parameter_EngineDataBase)
                        {
                            case "Oracle": // Oracle = CLOB!!
                                if (equalityOperator == SqlEqualityOperator.Equal)
                                    l_sEqualityOperator = " like ";
                                else
                                    l_sEqualityOperator = " not like ";
                                break;
                        }

                        l_oRet = Epure(p_oValue);
                        l_oRet = DoubleQuote(l_oRet);
                        l_oRet = EncapsulateQuote(l_oRet);

                        l_oRet = l_sLogicalOperator + l_sFullIndexName + l_sEqualityOperator + l_oRet;

                        break;

                    case 5:     //IndexDomainServiceEnums.IndexType.Users:
                    case 6:     //IndexDomainServiceEnums.IndexType.Boolean:
                    case 8:     //IndexDomainServiceEnums.IndexType.ListOfValues:
                    case 9:     //IndexDomainServiceEnums.IndexType.DocumentType:
                    case 13:    //IndexDomainServiceEnums.IndexType.TypeFlow: 
                    case 14:    //IndexDomainServiceEnums.IndexType.TypeChrono: 
                        l_oRet = Epure(p_oValue);
                        l_oRet = DoubleQuote(l_oRet);
                        l_oRet = EncapsulateQuote(l_oRet);

                        l_oRet = l_sLogicalOperator + l_sFullIndexName + l_sEqualityOperator + l_oRet;
                        break;

                    case 2:     //IndexDomainServiceEnums.IndexType.Integer:
                    case 3:     //IndexDomainServiceEnums.IndexType.Double:

                        if (p_oValue != string.Empty)
                        {
                            switch (SettingsManager.cStoreSettings.Parameter_EngineDataBase)
                            {
                                case "Oracle":
                                    if (equalityOperator == SqlEqualityOperator.Equal)
                                        l_sEqualityOperator = " like ";
                                    else
                                        l_sEqualityOperator = " not like ";
                                    l_oRet = l_sLogicalOperator + l_sFullIndexName + l_sEqualityOperator + "'" + p_oValue + "'";
                                    break;
                                default:
                                    l_oRet = l_sLogicalOperator + l_sFullIndexName + l_sEqualityOperator + p_oValue;
                                    break;
                            }
                        }
                        break;

                    case 4:     //IndexDomainServiceEnums.IndexType.Date:

                        if (p_oValue.Contains("%"))
                        {
                            //Getting the date
                            string firtPart = p_oValue.Substring(0, p_oValue.IndexOf('/'));
                            p_oValue = p_oValue.Substring(p_oValue.IndexOf('/') + 1, p_oValue.Length - p_oValue.IndexOf('/') - 1);

                            string secondPart = p_oValue.Substring(0, p_oValue.IndexOf('/'));
                            string thirdPart = p_oValue.Substring(p_oValue.IndexOf('/') + 1, p_oValue.Length - p_oValue.IndexOf('/') - 1);

                            //
                            bool addlogicaloperatorForAdvancedSearch = false;
                            if (string.IsNullOrEmpty(l_sLogicalOperator))
                            {
                                addlogicaloperatorForAdvancedSearch = true;
                            }

                            switch (SettingsManager.cStoreSettings.Parameter_EngineDataBase)
                            {
                                case "Oracle":
                                    if (!firtPart.Equals("%"))
                                    {
                                        if(System.Threading.Thread.CurrentThread.CurrentUICulture.Name !=  "fr")
                                        {
                                            l_oRet = l_sLogicalOperator + " extract(year from " + l_sFullIndexName + ") " + l_sEqualityOperator + " " + firtPart;    
                                        }
                                        else
                                        {
                                            l_oRet = l_sLogicalOperator + " extract(day from " + l_sFullIndexName + ") " + l_sEqualityOperator + " " + firtPart; 
                                        }
                                    }

                                    if (!secondPart.Equals("%"))
                                    {
                                        if (addlogicaloperatorForAdvancedSearch && !string.IsNullOrEmpty(l_oRet))
                                        {
                                            l_sLogicalOperator = " AND ";
                                        }
                                        l_oRet += l_sLogicalOperator + " extract(month from " + l_sFullIndexName + ") " + l_sEqualityOperator + " " + secondPart;
                                    }

                                    if (!thirdPart.Equals("%"))
                                    {
                                        if (addlogicaloperatorForAdvancedSearch && !string.IsNullOrEmpty(l_oRet))
                                        {
                                            l_sLogicalOperator = " AND ";
                                        }
                                        if (System.Threading.Thread.CurrentThread.CurrentUICulture.Name != "fr")
                                        {
                                            l_oRet += l_sLogicalOperator + " extract(day from " + l_sFullIndexName + ") " + l_sEqualityOperator + " " + thirdPart;
                                        }
                                        else
                                        {
                                            l_oRet += l_sLogicalOperator + " extract(year from " + l_sFullIndexName + ") " + l_sEqualityOperator + " " + thirdPart;
                                        }
                                    }
                                    break;
                                default:
                                    if (!firtPart.Equals("%"))
                                    {
                                        if (System.Threading.Thread.CurrentThread.CurrentUICulture.Name != "fr")
                                        {
                                            l_oRet = l_sLogicalOperator + " year(" + l_sFullIndexName + ") " + l_sEqualityOperator + " " + firtPart;
                                        }
                                        else
                                        {
                                            l_oRet = l_sLogicalOperator + " day(" + l_sFullIndexName + ") " + l_sEqualityOperator + " " + firtPart;
                                        }
                                    }

                                    if (!secondPart.Equals("%"))
                                    {
                                        if (addlogicaloperatorForAdvancedSearch && !string.IsNullOrEmpty(l_oRet))
                                        {
                                            l_sLogicalOperator = " AND ";
                                        }
                                        l_oRet += l_sLogicalOperator + " month(" + l_sFullIndexName + ") " + l_sEqualityOperator + " " + secondPart;
                                    }

                                    if (!thirdPart.Equals("%"))
                                    {
                                        if (addlogicaloperatorForAdvancedSearch && !string.IsNullOrEmpty(l_oRet))
                                        {
                                            l_sLogicalOperator = " AND ";
                                        }

                                        if (System.Threading.Thread.CurrentThread.CurrentUICulture.Name != "fr")
                                        {
                                            l_oRet += l_sLogicalOperator + " day(" + l_sFullIndexName + ") " + l_sEqualityOperator + " " + thirdPart;
                                        }
                                        else
                                        {
                                            l_oRet += l_sLogicalOperator + " year(" + l_sFullIndexName + ") " + l_sEqualityOperator + " " + thirdPart;
                                        }
                                    }
                                    
                                    break;
                            }
                        }
                        else
                        {
                            l_oRet = Epure(p_oValue);
                            l_oRet = DoubleQuote(l_oRet);
                            l_oRet = EncapsulateQuote(l_oRet);

                            switch (SettingsManager.cStoreSettings.Parameter_EngineDataBase)
                            {
                                case "Oracle":
                                    l_oRet = l_sLogicalOperator + l_sFullIndexName + l_sEqualityOperator + " to_date(" + l_oRet + ", 'dd/mm/yyyy') ";
                                    break;
                                default:
                                    l_oRet = l_sLogicalOperator + l_sFullIndexName + l_sEqualityOperator + l_oRet;
                                    break;
                            }
                        }
                        break;
                    default:
                        break;
                        
                }
            }

            return l_oRet;
        }

        /// <summary>
        /// Convert a list of field index values to SQL search syntax
        /// </summary>
        /// <param name="p_oSource">Index extention</param>
        /// <param name="p_oCriteria">List of values of the index</param>
        /// <param name="p_sTableName">Table name in database</param>
        /// <param name="equalityOperator">Equality operator to use between values</param>
        /// <param name="logicalOperator">Logical operator to use between index</param>
        /// <returns>Exemple: AND (TD001_CH001='test' OR TD001_CH001='123')</returns>
        public static string ValueForSQLSearch(this Index p_oSource, AdvancedSearchCriteriaItem p_oCriteria, string p_sTableName, SqlLogicalOperator logicalOperator = SqlLogicalOperator.AND, SqlEqualityOperator equalityOperator = SqlEqualityOperator.Equal)
        {
            if (p_oCriteria == null)
                throw new ArgumentNullException("Cannot be null!");

            //No query
            if (p_oCriteria.CriteriaValue.Count == 0)
            {
                return String.Empty;
            }

            StringBuilder sb = new StringBuilder();
            if (equalityOperator == SqlEqualityOperator.Between)
            {
                //between case
                //>={0} AND {1}<=
                sb.AppendFormat("{0} {1} ", ValueForSQLSearch(p_oSource, p_oCriteria.CriteriaValue[0], p_sTableName, SqlLogicalOperator.None, SqlEqualityOperator.SuperiorOrEqual), SqlLogicalOperator.AND.szGetDescription());
                if (p_oCriteria.CriteriaValue.Count > 1)
                    sb.AppendFormat("{0} {1} ", ValueForSQLSearch(p_oSource, p_oCriteria.CriteriaValue[1], p_sTableName, SqlLogicalOperator.None, SqlEqualityOperator.InferiorOrEqual), SqlLogicalOperator.AND.szGetDescription());
            }
            else
            {
                foreach (string value in p_oCriteria.CriteriaValue)
                {
                    switch (equalityOperator)
                    {
                        case SqlEqualityOperator.Different:
                            //Add Value like this: "TD001_CH001<operator>{value} AND "
                            sb.AppendFormat("{0} {1} ", ValueForSQLSearch(p_oSource, value, p_sTableName, SqlLogicalOperator.None, equalityOperator), SqlLogicalOperator.AND.szGetDescription());
                            break;
                        default:
                            //Add Value like this: "TD001_CH001<operator>{value} OR "
                            sb.AppendFormat("{0} {1} ", ValueForSQLSearch(p_oSource, value, p_sTableName, SqlLogicalOperator.None, equalityOperator), SqlLogicalOperator.OR.szGetDescription());
                            break;
                    }

                }
            }

            //remove last OR - AND comparator
            sb.Remove(sb.Length - 4, 4); 

            //Add AND statement to the query
            if (!string.IsNullOrEmpty(sb.ToString().Trim()))
            {
                return string.Format(" {0} ({1}) ", logicalOperator.szGetDescription(), sb.ToString());
            }
            else
            {
                return String.Empty;
            }
        }

        /// <summary>
        /// Convert a field index value to SQL syntax
        /// </summary>
        /// <param name="p_oSource">Index extention</param>
        /// <param name="p_oValue">Value of the index</param>
        /// <param name="isInsertMode">Indicate whether the conversion is for an insert query. In RIO some fields cannot be NULLABLE in database...</param>
        /// <returns>Return correct syntax</returns>
        public static string ValueForSQL(this Index p_oSource, string p_oValue, bool isInsertMode = false)
        {
            if (p_oSource == null)
                throw new ArgumentException("Parameter source can not be null.");

            string l_oRet = string.Empty;

            switch (p_oSource.Type)
            {
                case 4:     //IndexDomainServiceEnums.IndexType.Date:
                    l_oRet = Epure(p_oValue);
                    l_oRet = DoubleQuote(l_oRet);
                    l_oRet = EncapsulateQuote(l_oRet);

                    switch (SettingsManager.cStoreSettings.Parameter_EngineDataBase)
                    {
                        case "Oracle":
                            if (p_oValue.Equals(string.Empty))
                                l_oRet = NullString;
                            else
                                l_oRet = " to_date(" + l_oRet + ", 'dd/mm/yyyy') ";
                            break;
                        default:
                            if (p_oValue.Equals(string.Empty) && !isInsertMode)
                                l_oRet = NullString;
                            else
                                l_oRet = EncapsulateQuote(p_oValue);
                            break;
                    }

                    break;

                case 1:     //IndexDomainServiceEnums.IndexType.String:
                case 7:     //IndexDomainServiceEnums.IndexType.IntegerString:
                case 5:     //IndexDomainServiceEnums.IndexType.Users:
                case 6:     //IndexDomainServiceEnums.IndexType.Boolean:
                case 8:     //IndexDomainServiceEnums.IndexType.ListOfValues:
                case 9:     //IndexDomainServiceEnums.IndexType.DocumentType:
                case 10:    //IndexDomainServiceEnums.IndexType.Groups:
                case 11:    //IndexDomainServiceEnums.IndexType.ListeOfRecipient:
                case 12:    //IndexDomainServiceEnums.IndexType.Object: 
                case 13:    //IndexDomainServiceEnums.IndexType.TypeFlow: 
                case 14:    //IndexDomainServiceEnums.IndexType.TypeChrono: 
                case 15:    //IndexDomainServiceEnums.IndexType.ListOfSenders: 
                    l_oRet = Epure(p_oValue);
                    l_oRet = DoubleQuote(l_oRet);
                    l_oRet = EncapsulateQuote(l_oRet);

                    switch (SettingsManager.cStoreSettings.Parameter_EngineDataBase)
                    {
                        case "Oracle":
                            if (p_oValue.Equals(string.Empty))
                                l_oRet = NullString;
                            break;
                    }

                    break;
                case 2:     //IndexDomainServiceEnums.IndexType.Integer:
                case 3:     //IndexDomainServiceEnums.IndexType.Double:
                    if (p_oValue.Equals(string.Empty) && !isInsertMode)

                        //FPE: For the moment and compatibility with RIO4, an integer field is not nullable. He's set to 0 by RIO Manager!
                        l_oRet = NullIntegerString;
                    else
                        l_oRet = EncapsulateQuote(p_oValue);
                    break;
                default:
                    break;
            }

            return l_oRet;
        }

        private static string Epure(string p_szSource)
        {
            return p_szSource.TrimStart(new[] { '\'' }).TrimEnd(new[] { '\'' });
        }
        private static string DoubleQuote(string p_szSource)
        {
            return p_szSource.Replace("'", "''");
        }
        private static string EncapsulateQuote(string p_szSource)
        {
            return "'" + p_szSource + "'";
        }

        #endregion
    }

    #endregion
}
